<?php defined('BASEPATH') or exit('No direct script access allowed');
class Report_m extends MY_Model
{
	public function __construct(){
		parent::__construct();
		$this->set_table_name("transaction");
	}

	public function getMemberWinLose($fromdate, $todate, $agentId, $affiliateId){
		$sql = $sql = "SELECT du.username, t.user_id, SUM(t.win_lose - t.stake + t.commission) AS member_win_lose , "
		." \nSUM(t.agent_win_lose) AS agent_win_lose, "
		." \nSUM(t.affiliate_win_lose + t.affiliate_commission ) AS affiliate_win_lose, "
		." \nSUM(t.stake - t.win_lose - t.commission - t.agent_win_lose - t.affiliate_win_lose - t.affiliate_commission) as host_win_lose "
		." \nFROM `l_transaction` t INNER JOIN l_user u ON t.user_id= u.user_id INNER JOIN default_users du ON du.id=t.user_id "
		." \nWHERE date(t.settle_date) BETWEEN '". $fromdate->format("Y-m-d"). "' AND '".$todate->format("Y-m-d")."' "
		." \nAND u.user_type ='USER' "
		." \nAND (t.status = 'WIN' or t.status= 'LOSE')";

		if((int)$agentId>0){
			$sql .=" \n AND t.agent_id = ".((int)$agentId);
		}

		if((int)$affiliateId>0){
			$sql .=" \n AND t.affiliate_id = ".((int)$affiliateId);
		}

		$sql.=" \nGROUP BY du.username, t.user_id";

		return $this->db->query($sql, array())->result();
	}

	public function getAgentWinLose($fromdate, $todate){
		$sql = "SELECT t.agent_id, du.username, SUM(t.agent_win_lose) AS agent_win_lose, "
		." \nSUM(t.stake - t.win_lose - t.commission - t.agent_win_lose - t.affiliate_win_lose - t.affiliate_commission) as host_win_lose "
		." \nFROM `l_transaction` t INNER JOIN l_user u ON t.user_id= u.user_id INNER JOIN default_users du on du.id = t.agent_id "
		." \nWHERE date(t.settle_date) BETWEEN ? AND ? "
		." \nAND u.user_type ='USER' "
		." \nAND (t.status = 'WIN' or t.status= 'LOSE')"
		." \nGROUP BY t.agent_id, du.username";

		return $this->db->query($sql, array($fromdate->format("Y-m-d"),$todate->format("Y-m-d")))->result();
	}

	public function getAffiliateWinLose($fromdate, $todate){
		$sql = "SELECT t.affiliate_id,du.username, SUM(t.affiliate_win_lose + t.affiliate_commission ) AS affiliate_win_lose, "
		." \nSUM(t.stake - t.win_lose - t.commission - t.agent_win_lose - t.affiliate_win_lose - t.affiliate_commission) as host_win_lose "
		." \nFROM `l_transaction` t INNER JOIN l_user u ON t.user_id= u.user_id INNER JOIN default_users du on du.id = t.affiliate_id "
		." \nWHERE date(t.settle_date) BETWEEN ? AND ? "
		." \nAND u.user_type ='USER' "
		." \nAND (t.status = 'WIN' or t.status= 'LOSE')"
		." \nGROUP BY t.affiliate_id, du.username";

		return $this->db->query($sql, array($fromdate->format("Y-m-d"),$todate->format("Y-m-d")))->result();
	}

	public function getMemberBalance($agentId, $affiliateId, $userId){
		$sql = "SELECT t.user_id AS user_id,du.username, SUM(t.win_lose - t.stake + t.commission) AS balance  "
		." \nFROM `l_transaction` t INNER JOIN l_user u ON t.user_id= u.user_id INNER JOIN default_users du on du.id = t.user_id "
		." \nWHERE u.user_type ='USER' ";
		if($agentId){
			$sql.=" \n AND t.agent_id = ".((int)$agentId);
		}
		if($affiliateId){
			$sql.=" \n AND t.affiliate_id = ".((int)$affiliateId);
		}
		if($userId){
			$sql.=" \n AND t.user_id = ".((int)$userId);
		}
		$sql.=" \nGROUP BY t.user_id,du.username";
		return $this->db->query($sql, array())->result();
	}

	public function getAgentBalance($agentId){
		$sql = "SELECT t.agent_id AS user_id,du.username, SUM("
		." \nCASE WHEN ("
		." \nt.status = 'WIN'"
		." \nOR t.status = 'LOSE'"
		." \n)"
		." \nAND u.user_type = 'USER'"
		." \nTHEN t.agent_win_lose + t.commission"
		." \nWHEN t.status = 'COMPLETE'"
		." \nAND u.user_type = 'AGENT'"
		." \nTHEN t.win_lose - t.stake"
		." \nELSE 0"
		." \nEND ) AS balance"
		." \nFROM `l_transaction` t"
		." \nINNER JOIN l_user u ON t.user_id = u.user_id INNER JOIN default_users du on du.id = t.agent_id ";
		if($agentId){
			$sql.=" \n AND (t.agent_id = ".((int)$agentId)." OR t.user_id = " .((int)$agentId).")";
		}
		$sql.=" \nGROUP BY t.agent_id ,du.username";

		return $this->db->query($sql, array())->result();
	}

	public function getAffiliateBalance($affiliateId){
		$sql = "SELECT t.affiliate_id AS user_id, du.username, SUM("
		." \nCASE WHEN ("
		." \nt.status = 'WIN'"
		." \nOR t.status = 'LOSE'"
		." \n)"
		." \nAND u.user_type = 'USER'"
		." \nTHEN t.affiliate_win_lose + t.affiliate_commission"
		." \nWHEN t.status = 'COMPLETE'"
		." \nAND u.user_type = 'AFFILIATE'"
		." \nTHEN t.win_lose - t.stake"
		." \nELSE 0"
		." \nEND ) AS balance"
		." \nFROM `l_transaction` t"
		." \nINNER JOIN l_user u ON t.user_id = u.user_id INNER JOIN default_users du on du.id = t.affiliate_id ";
		if($affiliateId){
			$sql.=" \n AND (t.affiliate_id = ".((int)$affiliateId)." OR t.user_id = " .((int)$affiliateId).")";
		}
		$sql.=" \nGROUP BY t.affiliate_id, du.username";

		return $this->db->query($sql, array())->result();
	}

	public function getBetList($fromdate, $todate, $completed, $transType,  $userId,$channelId){

		$params=array();
		$sql="SELECT du.username, t.*, c.name AS channel_name"
		." \nFROM `l_transaction` t INNER JOIN l_user u ON t.user_id= u.user_id INNER JOIN default_users du ON du.id=u.user_id INNER JOIN l_channel c ON c.id=t.channel_id"
		." \nWHERE u.user_type ='USER' ";
		$sql.=$completed
		?" \nAND (t.status = 'WIN' or t.status= 'LOSE')"
		:" \nAND t.status = 'RUNNING'";
		if($fromdate&&$todate){
			$sql.="AND date(t.settle_date) BETWEEN ? AND ? ";
			$params[]=$fromdate->format("Y-m-d");
			$params[]=$todate->format("Y-m-d");
		}
		if($transType){
			$sql.=" \nAND t.trans_type = ?";
			$params[]=$transType;
		}
		if($userId){
			$sql.=" \nAND t.user_id = ?";
			$params[]=$userId;
		}
		if($channelId){
			$sql.=" \nAND t.channel_id = ?";
			$params[]=$channelId;
		}
		$sql.="\n ORDER BY t.trans_date";

		return $this->db->query($sql, $params)->result();
	}
	
	public function getBetListOfAllChannel($fromdate, $todate, $completed, $userId){
		$params=array();
		$sql="SELECT du.username, t.*, bt.label as bet_type, c.name as channel
			  FROM l_transaction t
			  		INNER JOIN l_channel c ON t.channel_id=c.id
			  		INNER JOIN l_bet_type bt ON t.trans_type=bt.bet_type
			  		INNER JOIN l_user u ON t.user_id= u.user_id 
			  		INNER JOIN default_users du ON du.id=u.user_id
		 	  WHERE u.user_type ='USER' ";
		$sql.=$completed
		?" \nAND (t.status = 'WIN' or t.status= 'LOSE')"
		:" \nAND (t.status = 'RUNNING' or t.status = 'WAITING')";
		if($fromdate&&$todate){
			$sql.="AND date(t.settle_date) BETWEEN ? AND ? ";
			$params[]=$fromdate->format("Y-m-d");
			$params[]=$todate->format("Y-m-d");
		}
		if($userId){
			$sql.=" \nAND t.user_id = ?";
			$params[]=$userId;
		}
		$sql.="\n ORDER BY t.trans_date DESC";
		return $this->db->query($sql, $params)->result();
	}
	
	public function getListDepositWithdraw($fromdate, $todate, $userId){
		$params=array();
		$sql="SELECT du.username, t.*
			  FROM l_transaction t
			  		INNER JOIN l_user u ON t.user_id= u.user_id 
			  		INNER JOIN default_users du ON du.id=u.user_id
		 	  WHERE u.user_type ='USER' AND trans_type IN ('DEPOSIT', 'WITHDRAW')";
		if($fromdate&&$todate){
			$sql.="AND date(t.settle_date) BETWEEN ? AND ? ";
			$params[]=$fromdate->format("Y-m-d");
			$params[]=$todate->format("Y-m-d");
		}
		if($userId){
			$sql.=" \nAND t.user_id = ?";
			$params[]=$userId;
		}
		$sql.="\n ORDER BY t.trans_date DESC";
		return $this->db->query($sql, $params)->result();
	}
}